using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace HouseDiyService
{
   internal class HouseDiyHelper
    {
       private static HouseDiyHelper instance = null;
       private HouseDiyHelper() { }
       public static HouseDiyHelper GetInstance()
       {
           return (instance != null) ? instance : new HouseDiyHelper();
       }

       private string _connectionString = ConfigurationManager.ConnectionStrings["HousediyConnectionString"].ConnectionString;
       public string ConnectionString
       {
           get { return _connectionString; }
           set { _connectionString = value; }
       }

       public DataSet FillDataSet(string sql, List<ForParams> pa)
       {
           using (SqlConnection conn = new SqlConnection(_connectionString))
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(sql, conn);
               if (pa != null) foreach (ForParams p in pa)
                   {
                       cmd.Parameters.Add(p.name, p.type, p.size);
                       cmd.Parameters[p.name].Value = p.value;
                   }
               DataSet ds = new DataSet();
               SqlDataAdapter adapter = new SqlDataAdapter(cmd);

               adapter.Fill(ds);
               return ds;
           }
       }

       public object ExecuteScalarDB(string sql, SqlParameter[] pars)
       {
           using (SqlConnection conn = new SqlConnection(_connectionString))
           {
               conn.Open();
               SqlCommand comm = new SqlCommand(sql, conn);
               if (pars != null) comm.Parameters.AddRange(pars);
               return comm.ExecuteScalar();
           }
       }

    }

     class ForParams
    {
        public ForParams(string name, SqlDbType type, int size, object value)
        {
            this.name = name;
            this.type = type;
            this.size = size;
            this.value = value;
        }
        public ForParams() { }
        public string name;
        public SqlDbType type;
        public int size;
        public object value;
    }
}
